package org.eiahe.hr.salary.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.eiahe.hr.salary.domain.vo.SalaryImportVo;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * 薪资Excel解析工具类
 * 专门用于处理复杂的合并单元格薪资模板
 *
 * @author zhangnn
 */
@Slf4j
public class SalaryExcelParser {

    /**
     * 解析复杂的薪资Excel模板
     *
     * @param inputStream Excel文件输入流
     * @return 解析后的薪资数据列表
     */
    public static List<SalaryImportVo> parseSalaryExcel(InputStream inputStream) {
        List<SalaryImportVo> resultList = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);

            // 获取总行数
            int rowCount = sheet.getLastRowNum();
            log.info("Excel总行数: {}", rowCount);

            // 从第4行开始读取数据（跳过3行表头）
            for (int i = 3; i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                // 解析一行数据
                SalaryImportVo vo = parseRowData(sheet, row);
                if (vo != null && vo.getEmployeeNo() != null && !vo.getEmployeeNo().trim().isEmpty()) {
                    resultList.add(vo);
                }
            }

            log.info("解析完成，共解析到 {} 条有效数据", resultList.size());
        } catch (Exception e) {
            log.error("解析Excel文件时发生错误", e);
            throw new RuntimeException("解析Excel文件时发生错误: " + e.getMessage(), e);
        }

        return resultList;
    }

    /**
     * 解析单行数据
     *
     * @param sheet Excel工作表
     * @param row   数据行
     * @return 解析后的SalaryImportVo对象
     */
    private static SalaryImportVo parseRowData(Sheet sheet, Row row) {
        SalaryImportVo vo = new SalaryImportVo();

        try {
            // A列: 序号（跳过）

            // B列: 部门
            Cell deptCell = getMergedRegionCell(sheet, row, 1);
            vo.setDepartment(getCellValueAsString(deptCell));

            // C列: 姓名
            Cell nameCell = getMergedRegionCell(sheet, row, 2);
            vo.setEmployeeName(getCellValueAsString(nameCell));

            // D列: 银行卡号
            Cell bankCardCell = getMergedRegionCell(sheet, row, 3);
            vo.setBankCardNo(getCellValueAsString(bankCardCell));

            // E列: 开户行
            Cell bankNameCell = getMergedRegionCell(sheet, row, 4);
            vo.setBankName(getCellValueAsString(bankNameCell));

            // F列: 职级工资
            Cell jobLevelCell = getMergedRegionCell(sheet, row, 5);
            vo.setJobLevelSalary(getCellValueAsBigDecimal(jobLevelCell));

            // G列: 基本工资
            Cell baseSalaryCell = getMergedRegionCell(sheet, row, 6);
            vo.setBaseSalary(getCellValueAsBigDecimal(baseSalaryCell));

            // H列: 岗位工资
            Cell positionSalaryCell = getMergedRegionCell(sheet, row, 7);
            vo.setPositionSalary(getCellValueAsBigDecimal(positionSalaryCell));

            // I列: 绩效工资
            Cell performanceSalaryCell = getMergedRegionCell(sheet, row, 8);
            vo.setPerformanceSalary(getCellValueAsBigDecimal(performanceSalaryCell));

            // J列: 职务津贴
            Cell dutyAllowanceCell = getMergedRegionCell(sheet, row, 9);
            vo.setDutyAllowance(getCellValueAsBigDecimal(dutyAllowanceCell));

            // K列: 应出勤天数
            Cell shouldAttendanceCell = getMergedRegionCell(sheet, row, 10);
            vo.setShouldAttendanceDays(getCellValueAsInteger(shouldAttendanceCell));

            // L列: 缺勤天数
            Cell absenceDaysCell = getMergedRegionCell(sheet, row, 11);
            vo.setAbsenceDays(getCellValueAsInteger(absenceDaysCell));

            // M列: 计薪出勤天数
            Cell paidAttendanceCell = getMergedRegionCell(sheet, row, 12);
            vo.setPaidAttendanceDays(getCellValueAsInteger(paidAttendanceCell));

            // N列: 缺勤扣款
            Cell absenceDeductionCell = getMergedRegionCell(sheet, row, 13);
            vo.setAbsenceDeduction(getCellValueAsBigDecimal(absenceDeductionCell));

            // O列: 误餐补助
            Cell mealAllowanceCell = getMergedRegionCell(sheet, row, 14);
            vo.setMealAllowance(getCellValueAsBigDecimal(mealAllowanceCell));

            // P列: 其他调整
            Cell otherAdjustmentCell = getMergedRegionCell(sheet, row, 15);
            vo.setOtherAdjustment(getCellValueAsBigDecimal(otherAdjustmentCell));

            // Q列: 餐费扣款
            Cell mealDeductionCell = getMergedRegionCell(sheet, row, 16);
            vo.setMealDeduction(getCellValueAsBigDecimal(mealDeductionCell));

            // R列: 应发合并
            Cell grossSalaryCell = getMergedRegionCell(sheet, row, 17);
            vo.setGrossSalary(getCellValueAsBigDecimal(grossSalaryCell));

            // S列: 养老扣款
            Cell pensionDeductionCell = getMergedRegionCell(sheet, row, 18);
            vo.setPensionDeduction(getCellValueAsBigDecimal(pensionDeductionCell));

            // T列: 失业扣款
            Cell unemploymentDeductionCell = getMergedRegionCell(sheet, row, 19);
            vo.setUnemploymentDeduction(getCellValueAsBigDecimal(unemploymentDeductionCell));

            // U列: 医疗扣款
            Cell medicalDeductionCell = getMergedRegionCell(sheet, row, 20);
            vo.setMedicalDeduction(getCellValueAsBigDecimal(medicalDeductionCell));

            // V列: 大额医疗扣款
            Cell majorMedicalDeductionCell = getMergedRegionCell(sheet, row, 21);
            vo.setMajorMedicalDeduction(getCellValueAsBigDecimal(majorMedicalDeductionCell));

            // W列: 社保个人扣款
            Cell socialInsuranceCell = getMergedRegionCell(sheet, row, 22);
            vo.setSocialInsurancePersonal(getCellValueAsBigDecimal(socialInsuranceCell));

            // X列: 公积金扣款
            Cell housingFundCell = getMergedRegionCell(sheet, row, 23);
            vo.setHousingFundPersonal(getCellValueAsBigDecimal(housingFundCell));

            // Y列: 社保公积金合计
            Cell socialHousingTotalCell = getMergedRegionCell(sheet, row, 24);
            vo.setSocialHousingTotal(getCellValueAsBigDecimal(socialHousingTotalCell));

            // Z列: 税前工资
            Cell taxableSalaryCell = getMergedRegionCell(sheet, row, 25);
            vo.setTaxableSalary(getCellValueAsBigDecimal(taxableSalaryCell));

            // AA列: 个人所得税
            Cell personalTaxCell = getMergedRegionCell(sheet, row, 26);
            vo.setPersonalTax(getCellValueAsBigDecimal(personalTaxCell));

            // AB列: 实发工资
            Cell netSalaryCell = getMergedRegionCell(sheet, row, 27);
            vo.setNetSalary(getCellValueAsBigDecimal(netSalaryCell));

            // 设置工号为行号（如果没有单独的工号列）
            if (vo.getEmployeeName() != null && !vo.getEmployeeName().trim().isEmpty()) {
                vo.setEmployeeNo("ROW_" + row.getRowNum());
            }

            log.debug("解析行数据: {}", vo);
        } catch (Exception e) {
            log.error("解析行数据时发生错误，行号: {}", row.getRowNum(), e);
            return null;
        }

        return vo;
    }

    /**
     * 获取单元格，处理可能的空行情况
     *
     * @param row       行对象
     * @param cellIndex 单元格索引
     * @return 单元格对象
     */
    private static Cell getCell(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        return cell;
    }

    /**
     * 获取合并区域的单元格值
     * 处理Excel中的合并单元格情况
     *
     * @param sheet     工作表
     * @param row       当前行
     * @param cellIndex 单元格索引
     * @return 单元格对象
     */
    private static Cell getMergedRegionCell(Sheet sheet, Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell != null) {
            return cell;
        }

        try {
            // 如果当前单元格为空，检查是否在合并区域内
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.isInRange(row.getRowNum(), cellIndex)) {
                    // 获取合并区域的第一个单元格
                    Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
                    if (firstRow != null) {
                        Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                        if (firstCell != null) {
                            return firstCell;
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.debug("检查合并区域时发生错误: {}", e.getMessage());
        }

        // 如果没有找到合并区域，创建新单元格
        return getCell(row, cellIndex);
    }

    /**
     * 获取单元格字符串值
     *
     * @param cell 单元格对象
     * @return 字符串值
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 数字转字符串，去掉小数点后的0
                    return new BigDecimal(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return null;
            default:
                return null;
        }
    }

    /**
     * 获取单元格BigDecimal值
     *
     * @param cell 单元格对象
     * @return BigDecimal值
     */
    private static BigDecimal getCellValueAsBigDecimal(Cell cell) {
        if (cell == null) {
            return null;
        }

        try {
            switch (cell.getCellType()) {
                case NUMERIC:
                    return BigDecimal.valueOf(cell.getNumericCellValue());
                case STRING:
                    String stringValue = cell.getStringCellValue();
                    if (stringValue != null && !stringValue.trim().isEmpty()) {
                        // 清理字符串，移除不可见字符
                        stringValue = stringValue.trim().replaceAll("[^0-9\\.\\-]", "");
                        if (!stringValue.isEmpty()) {
                            return new BigDecimal(stringValue);
                        }
                    }
                    return null;
                default:
                    return null;
            }
        } catch (NumberFormatException e) {
            log.warn("无法将单元格值转换为数字: {} (单元格地址: {})，错误信息: {}",
                    cell.toString(),
                    cell.getAddress() != null ? cell.getAddress().formatAsString() : "未知",
                    e.getMessage());
            return null;
        } catch (Exception e) {
            log.warn("解析单元格时发生未知错误: {} (单元格地址: {})，错误信息: {}",
                    cell.toString(),
                    cell.getAddress() != null ? cell.getAddress().formatAsString() : "未知",
                    e.getMessage());
            return null;
        }
    }

    /**
     * 获取单元格Integer值
     *
     * @param cell 单元格对象
     * @return Integer值
     */
    private static Integer getCellValueAsInteger(Cell cell) {
        BigDecimal decimalValue = getCellValueAsBigDecimal(cell);
        return decimalValue != null ? decimalValue.intValue() : null;
    }
}